"use strict";
const dateFns = require("date-fns");
const moment = require("moment");
const rds = require("./rds.js");
const mta = require("./mta.js");
const axios = require("axios");
const logger = require("./logger");
const task = require("./task");
const wework = require('./wework');
const cache = require('./cache');

const mtaCodeMapping = {
    mta: "",
    asd: "subject-autism-spectrum-disorder",
    eeac: "epilepsy-encephalitis-and-comorbidities",
    cndd: "childhood-neuro-developmental-disorder",
    adhd: "subject-attention-deficit-hyperactivity-disorder",
    ts: "subject-tourette-syndrome",
    ntft: "subject-nutritional-therapy-for-tumors",
};

//为了兼容mall系统中的历史代码
const mtaCodeAlias = {
    mta: "mta",
    bgba: "mta",
    asd: "asd",
    eeac: "eeac",
    bgbaeeac: "eeac",
    bgbaeac: "eeac",
    cndd: "cndd",
    bgbacndd: "cndd",
    adhd: "adhd",
    ts: "ts",
    ntft: "ntft",
};

//code: author || blog || pre-defined-Codes
//cid 只在code是author的时候起作用，通过cid锁定author主页，需要配置
//path只在subject_code是blog的时候起作用
const getMtaSubject = async function (code, cid = "NaN", path = "") {
    // 确保 path 是字符串类型
    path = String(path || "");

    if (code === "author" || path.startsWith("/author/")) {
        let sql = `SELECT name, bio, slug FROM mta.users WHERE website like '%refer?dest_code=author&rid=${cid}'`;
        if (path.startsWith("/author/")) {
            const authorSlug = path.replace("/author/", "").replace("/", ""); //去掉可能的尾巴上的/
            sql = `SELECT name, bio, slug FROM mta.users WHERE slug = '${authorSlug}'`;
        }
        const [results] = await mta.query(sql);
        if (results.length > 0) {
            const record = results[0];
            return {
                code: code,
                background: "https://totemlife.cn/apps/public/background-2.png",
                title: record.name,
                description: record.bio,
                comment: "微生态医学成果汇编",
                slug: `author/${record.slug}`,
                url: `https://mtx.totemlife.cn/author/${record.slug}`,
                encodedUrl: encodeURIComponent(
                    `https://mtx.totemlife.cn/author/${record.slug}`
                ),
            };
        }
    }

    let slug = "";
    if (code === "blog" && path !== "") {
        slug = path.replace(/^\/+|\/+$/g, ""); // Remove leading and trailing slashes
    }
    if (code in mtaCodeAlias) {
        const alias = mtaCodeAlias[code];
        slug = mtaCodeMapping[alias];
    }

    if (slug !== "") {
        const [blogResults] = await mta.query(
            `SELECT title, custom_excerpt FROM mta.posts WHERE slug = ?`,
            [slug]
        );
        if (blogResults.length > 0) {
            const blogRecord = blogResults[0];
            return {
                code: code,
                title: blogRecord.title,
                background: "https://totemlife.cn/apps/public/background-2.png",
                description: blogRecord.custom_excerpt,
                comment: "扫码查看详细内容",
                slug: path,
                url: `https://mtx.totemlife.cn/${slug}`,
                encodedUrl: encodeURIComponent(
                    `https://mtx.totemlife.cn/${slug}`
                ),
            };
        }
    }

    return {
        code: code,
        title: "二代菌临床前沿",
        description: "疾病专题",
        background: "https://totemlife.cn/apps/public/background-2.png",
        comment: "扫码查看详细内容",
        slug: slug,
        url: `https://mtx.totemlife.cn/`,
        encodedUrl: encodeURIComponent(`https://mtx.totemlife.cn/`),
    };
};

const getMtaUrlByCode = async function (code, cid = "NaN", path = "") {
    const subject = await getMtaSubject(code, cid, path);
    if (subject.url) return subject.url;
    return "https://mtx.totemlife.cn/";
};

const getSlugBasedOnDate = async (currentDate) => {
    const startDate = moment("2023-08-11", "YYYY-MM-DD");
    const daysDiff = currentDate.diff(startDate, "days");

    const sql = `SELECT slug FROM mta.posts WHERE status='published' ORDER BY updated_at ASC`;
    const [rows] = await mta.query(sql);

    const recordNumber = daysDiff % rows.length;
    const selectedSlug = rows[recordNumber].slug;

    return selectedSlug;
};

const getMostRecentSlug = async () => {
    const sql = `SELECT slug FROM mta.posts WHERE status='published' ORDER BY updated_at DESC limit 1`;
    const [rows] = await mta.query(sql);

    const selectedSlug = rows[0].slug;

    return selectedSlug;
};

var registerMpAuth = async function (uuid) {
    const currentTime = Math.floor(Date.now() / 1000); // 获取当前时间的Unix时间戳

    //首先清除已经超过五分钟的纪录
    let sql = `
        UPDATE tutem_mall.mp_authentication SET is_used = 0, temp_uuid = null, apply_start_time = null, wx_server_response =null
        WHERE is_used = 1 AND (apply_start_time < ${currentTime - 300} or apply_start_time is null)`;
    await rds.query(sql);

    //尚在时效范围内给特定uuid的纪录继续保留有效
    sql = `SELECT * FROM tutem_mall.mp_authentication WHERE is_used = 1 and temp_uuid = ? LIMIT 1`;
    let [rows] = await rds.query(sql, [uuid]);
    if (rows.length > 0) return rows[0];

    sql = `UPDATE tutem_mall.mp_authentication SET is_used = 1, apply_start_time = ${currentTime}, temp_uuid = ?  WHERE is_used = 0 ORDER BY id ASC LIMIT 1`;
    await rds.query(sql, [uuid]);

    sql = `SELECT * FROM tutem_mall.mp_authentication WHERE is_used = 1 and temp_uuid = ? LIMIT 1`;
    [rows] = await rds.query(sql, [uuid]);
    if (rows.length > 0) return rows[0];

    return null;
};

async function genWeappUrlLink(args = {
    path: "/pages/user_phone/index",
    query: "spreader_uid=14965&code=20240429",
    is_expire: true,
}) {
    const token = await wework.getWechatAccessToken("weapp");
    const { path, query, is_expire } = args;
    const response = await axios.post(
        `https://api.weixin.qq.com/wxa/generate_urllink?access_token=${token}`,
        args
    );
    return response?.data?.url_link ?? null;
}

// 获取企业成员列表
async function getEmployeeList(agentId = 2000003) {
    const accessToken = await wework.getWeworkAccessToken(agentId);
    const url = `https://qyapi.weixin.qq.com/cgi-bin/user/simplelist?access_token=${accessToken}&department_id=1&fetch_child=1`;
    const response = await axios.get(url);
    const userList = response.data.userlist;
    return userList;
}

//获取特定企业微信用户的外部联系人
async function getExternalUserids(userId, agentId = 2000003) {
    const accessToken = await wework.getWeworkAccessToken(agentId);
    const {
        data: { external_userid },
    } = await axios.get(
        `https://qyapi.weixin.qq.com/cgi-bin/externalcontact/list?access_token=${accessToken}&userid=${userId}`
    );
    return external_userid;
}

//获取外部联系人的详细信息
async function getExternalContact(external_userid, agentId = 2000003) {
    const accessToken = await wework.getWeworkAccessToken(agentId);
    const { data } = await axios.get(
        `https://qyapi.weixin.qq.com/cgi-bin/externalcontact/get?access_token=${accessToken}&external_userid=${external_userid}`
    );
    return data;
}

async function getAllExternalContacts(agentId = 2000003) {
    const allExternalUserids = [];
    const allExternalContacts = [];
    const users = await getEmployeeList(agentId);
    for (const user of users) {
        const externalUserids = await getExternalUserids(user.userid, agentId);
        allExternalUserids.push(...externalUserids);
    }
    for (const external_userid of allExternalUserids) {
        const externalContact = await getExternalContact(
            external_userid,
            agentId
        );
        allExternalContacts.push(externalContact);
    }
    return allExternalContacts;
}

function getSubjectTemplate(subject) {
    const subjectTemplates = {
        greeting: {
            tips: '"节日是闪耀的瞬间，生命的喧嚣沉寂，人们获得自由的契机，展现自己的本真，追求无拘无束的欢愉。" - 图图',
            title: "祝您佳节愉快",
            background: "https://totemlife.cn/apps/default-image",
            message: "时间不是药，药在时间里",
            description:
                "凡业已圆满者，皆为向死；凡依旧青涩者，乃念久长。身陷苦难，终求苟活，唯愿圆融愉恰，高远久长，乃至璀璨。",
            comment:
                '"节日是闪耀的瞬间，生命的喧嚣沉寂，人们获得自由的契机，展现自己的本真，追求无拘无束的欢愉。" - 图图',
            qrImg: "https://oss.foodtrust.cn//9ae54202311260915318559.png", //这个是U#676的蓝色公众号关注码
            refNo: "创作节日贺卡",
        },
        "bind-wework-okey": {
            title: "图腾生命医学应用提示",
            tips: "企微绑定官网小程序完成，您可以回到企微重新点击进入",
            background: "https://totemlife.cn/apps/public/background-2.png",
            message: "企微绑定官方小程序成功",
            description: "请回到企微打开原有应用即可",
            comment: "每个人都应该通过工作来体现自己的价值观和使命感",
            qrImg: "https://oss.foodtrust.cn//9ae54202311260915318559.png", //这个是U#676的蓝色公众号关注码
            refNo: "关注开启通知",
        },
        "LTR-2024-01-01": {
            title: "新年伊始共庆元旦",
            background: "https://oss.foodtrust.cn//aede3202401010748414861.jpg",
            textStyle: "style='color:white;'",
            message: "新年伊始共庆元旦金豆抽奖",
            description:
                "100%中奖，图腾益生、益生菌咖啡、代金券等共2024份大奖等你来抽",
            tips: "新年已至，成就更健康的自己和他人",
            comment: "扫码可获赠2024健康金豆",
            qrImg: "https://oss.foodtrust.cn//0af03202309272240581217.png", //这个是U#1414的蓝色公众号关注码
            refNo: "扫码领取健康金豆",
            bntName: "扫码领取健康金豆",
            bntStyle: "",
            bntLink: "/apps/loyalty/scratch-card/LTR-2024-01-01",
            counterStyle:
                " style='color:#dc3023;-webkit-text-stroke:1px#dc3023;'",
            startTime: "2024-01-01T00:00:00",
            stopTime: "2024-01-01T23:59:59",
        },
        "LTR-2024-12-30": {
            title: "迎新年抽奖活动",
            background: "https://oss.foodtrust.cn//aede3202401010748414861.jpg",
            textStyle: "style='color:white;'",
            message: "迎新年抽奖活动",
            description: "健康金豆纳好运，福寿安康满新年",
            tips: "新年已至，成就更健康的自己和他人",
            comment: "扫码关注图腾生命医学",
            qrImg: "https://oss.foodtrust.cn//0af03202309272240581217.png", //这个是U#1414的蓝色公众号关注码
            refNo: "点击开始抽奖",
            bntName: "点击开始抽奖",
            bntStyle: "",
            bntLink: "/apps/loyalty/scratch-card/LTR-2024-12-30",
            counterStyle:
                " style='color:#dc3023;-webkit-text-stroke:1px#dc3023;'",
            startTime: "2024-12-30T08:00:00",
            stopTime: "2025-01-02T23:59:59",
        },
        "LTR-2024-08-19": {
            title: "2024中国医师节抽奖活动",
            background: "https://oss.foodtrust.cn//aede3202401010748414861.jpg",
            textStyle: "style='color:white;'",
            message: "2024中国医师节抽奖活动",
            description: "100%中奖，图腾星悦瞳和高额健康金豆等你来抽",
            tips: "感谢您守护我们的健康，也请您照顾好自己和家人",
            comment: "扫码可获赠2024健康金豆",
            qrImg: "https://oss.foodtrust.cn//2d55d202401312018579476.png", //这个是U#660的蓝色公众号关注码
            refNo: "扫码进入活动页面",
            bntName: "领取开工利是",
            bntStyle: "",
            bntLink: "/apps/loyalty/scratch-card/LTR-2024-08-19",
            counterStyle:
                " style='color:#dc3023;-webkit-text-stroke:1px#dc3023;'",
            startTime: "2024-08-19T00:00:00",
            stopTime: "2024-08-19T23:59:59",
        },
        "GFT-2024-02-18": {
            title: "图腾生命医学甲辰年开工利是",
            background: "https://oss.foodtrust.cn//ef1a9202402181313597480.png",
            textStyle: "",
            message: "图腾生命医学甲辰年开工利是",
            description: "节庆贺仪等您登记领取",
            tips: "愿君家和身心安",
            comment: "共行益生济世途",
            qrImg: "https://oss.foodtrust.cn//0af03202309272240581217.png", //这个是U#1414的蓝色公众号关注码
            refNo: "扫码进入活动页面",
            bntName: "领取开工利是",
            bntStyle: " style='background-color:#dc3023;'",
            bntLink: "/apps/loyalty/choose-gift/GFT-2024-02-18",
            counterStyle:
                " style='color:#dc3023;-webkit-text-stroke:1px#dc3023;'",
            startTime: "2024-02-18T00:00:00",
            stopTime: "2024-02-19T23:59:59",
        },
        "GFT-2024-05-08": {
            title: "图腾纾新内部试用反馈意见征集",
            background:
                "https://oss.foodtrust.cn/photo-1599939571322-792a326991f2-800x1120.png", //月亮海报封面
            textStyle: "",
            message: "图腾纾新内部试用反馈意见征集",
            description:
                "请提供真实的使用体验和观察指标，帮助我们继续提高产品和服务品质、精益求精！",
            tips: "愿君家和身心安",
            comment: "共行益生济世途",
            qrImg: "https://oss.foodtrust.cn//0af03202309272240581217.png", //这个是U#1414的蓝色公众号关注码
            refNo: "扫描关注公众号",
            bntName: "交反馈得金豆",
            bntStyle: " style='background-color:#0f4c81;'",
            bntLink:
                "https://doc.weixin.qq.com/forms/AIEAOAfVAAgAXcAgwbpAIsubcYQnb7Qpf",
            counterStyle:
                " style='color:#dc3023;-webkit-text-stroke:1px#dc3023;'",
            startTime: "2024-05-08T00:00:00",
            stopTime: "2024-06-28T23:59:59",
        },
        "SURVEY-14784502": {
            title: "图腾生命医学2024H1用户随访",
            background:
                "https://oss.foodtrust.cn/photo-1599939571322-792a326991f2-800x1120.png", //月亮海报封面
            textStyle: "",
            message: "图腾生命医学2024H1用户随访",
            description:
                "请提供真实的使用体验和客观指标，帮助我们不断提高产品服务品质，谢谢！",
            tips: "愿君家和身心安",
            comment: "共行益生济世途",
            qrImg: "https://oss.foodtrust.cn//0af03202309272240581217.png", //这个是U#1414的蓝色公众号关注码
            refNo: "扫描关注公众号",
            bntName: "查看本期结果",
            bntStyle: " style='background-color:#0f4c81;'",
            bntLink:
                "https://totemlife.cn/apps/loyalty/survey-results/14784502",
            counterStyle:
                " style='color:#dc3023;-webkit-text-stroke:1px#dc3023;'",
            startTime: "2024-06-16T00:00:00",
            stopTime: "2024-06-17T23:59:59",
        },
        "GFT-2024-01-26": {
            title: "辞旧迎新共迎甲辰",
            background: "https://oss.foodtrust.cn//56003202401260812414239.jpg",
            textStyle: "",
            message: "图腾生命医学癸卯岁末贵宾贺仪",
            description: "图腾益生、山姆会员卡等岁末贵宾贺仪等您登记领取",
            tips: "愿君家和身心安",
            comment: "共行益生济世途",
            qrImg: "https://oss.foodtrust.cn//0af03202309272240581217.png", //这个是U#1414的蓝色公众号关注码
            refNo: "扫码进入活动页面",
            bntName: "选取贺仪",
            bntStyle: " style='background-color:#dc3023;'",
            bntLink: "/apps/loyalty/choose-gift/GFT-2024-01-26",
            counterStyle:
                " style='color:#dc3023;-webkit-text-stroke:1px#dc3023;'",
            startTime: "2024-01-26T00:00:00",
            stopTime: "2024-02-04T23:59:59",
        },
        "LTR-2024-02-09": {
            title: "迎甲辰许新愿抽好礼",
            background: "https://oss.foodtrust.cn//3ad1b202402090704242687.jpg",
            textStyle: "style='color:#ffff00;-webkit-text-stroke:1px#ffff00;'",
            message: "迎甲辰许新愿得好礼",
            description: "商城金豆、图腾益生等节庆好礼等您领取",
            tips: "愿君家和身心安",
            comment: "共行益生济世途",
            qrImg: "https://oss.foodtrust.cn//0af03202309272240581217.png", //这个是U#1414的蓝色公众号关注码
            refNo: "扫码进入活动页面",
            bntName: "点击进入抽奖活动",
            bntStyle: " style='background-color:#dc3023;'",
            bntLink: "/apps/loyalty/scratch-card/LTR-2024-02-09",
            counterStyle:
                " style='color:#dc3023;-webkit-text-stroke:1px#dc3023;'",
            startTime: "2024-02-09T00:00:00",
            stopTime: "2024-02-15T23:59:59",
        },
        "LTR-2023-09-25": {
            title: "月揽芳华共迎接中秋抽奖活动",
            background:
                "https://oss.foodtrust.cn/photo-1599939571322-792a326991f2-800x1120.png",
            message: "月揽芳华共迎接中秋抽奖活动",
            textStyle: "style='color:white;'",
            description:
                "扫码参加金豆抽奖活动，图腾益生粉、益生菌咖啡、代金券等共2024份大奖等你来抽",
            tips: "",
            comment: "",
            qrImg: "https://oss.foodtrust.cn//0af03202309272240581217.png", //这个是U#1414的蓝色公众号关注码
            refNo: "扫码领取健康金豆",
            bntName: "点击进入抽奖活动",
            bntStyle: " style='background-color:#dc3023;'",
            bntLink: "/apps/loyalty/scratch-card/LTR-2023-09-25",
            counterStyle:
                " style='color:#dc3023;-webkit-text-stroke:1px#dc3023;'",
            startTime: "2023-09-25T00:00:00",
            stopTime: "2023-09-30T23:59:59",
        },
        "LTR-2024-09-10": {
            title: "月揽芳华迎中秋抽奖活动",
            background:
                "https://oss.foodtrust.cn/photo-1599939571322-792a326991f2-800x1120.png",
            message: "月揽芳华迎中秋抽奖活动",
            textStyle: "style='color:white;'",
            description:
                "扫码参加金豆抽奖活动，图腾益生菌、健康金豆等共2024份大奖等你来抽",
            tips: "",
            comment: "",
            qrImg: "https://oss.foodtrust.cn//0af03202309272240581217.png", //这个是U#1414的蓝色公众号关注码
            refNo: "扫码领取金豆",
            bntName: "点击进入活动",
            bntStyle: " style='background-color:#0f4c81;'",
            bntLink: "/apps/loyalty/scratch-card/LTR-2024-09-10",
            counterStyle:
                " style='color:#0f4c81;-webkit-text-stroke:1px#dc3023;'",
            startTime: "2024-09-10T00:00:00",
            stopTime: "2024-09-14T23:59:59",
        },
        "LTR-2024-02-01": {
            title: "2024新春锦礼金豆抽奖活动",
            background: "https://oss.foodtrust.cn//dde37202401311825244290.jpg", //活动海报封面
            message: "2024新春锦礼金豆抽奖活动",
            textStyle: "style='color:#ffff00;-webkit-text-stroke:1px#ffff00;'",
            description:
                "扫码参加金豆抽奖活动，图腾益生、益生菌咖啡、代金券等共2024份大奖等你来抽",
            tips: "",
            comment: "",
            qrImg: "https://oss.foodtrust.cn//2d55d202401312018579476.png", //这个是U#660的蓝色公众号关注码
            refNo: "扫码参加节庆活动",
            bntName: "点击进入抽奖活动",
            bntStyle: " style='background-color:#dc3023;'",
            bntLink: "/apps/loyalty/scratch-card/LTR-2024-02-01",
            counterStyle:
                " style='color:#dc3023;-webkit-text-stroke:1px#dc3023;'",
            startTime: "2024-02-01T00:00:00",
            stopTime: "2024-02-05T23:59:59",
        },
        "LTR-2024-03-05": {
            title: "国际劳动妇女节100%幸运抽奖活动",
            background: "https://oss.foodtrust.cn//c0ac1202403050135302535.jpg",
            message: "",
            textStyle: "style='color:white;-webkit-text-stroke:1px#ffff00;'",
            description: "",
            tips: "",
            comment: "",
            qrImg: "https://oss.foodtrust.cn//2d55d202401312018579476.png", //这个是U#660的蓝色公众号关注码
            refNo: "扫码领取健康金豆",
            bntName: "点击进入抽奖活动",
            bntStyle: " style='background-color:#dc3023;'",
            bntLink: "/apps/loyalty/scratch-card/LTR-2024-03-05",
            counterStyle:
                " style='color:#ffffff;-webkit-text-stroke:1px#ffffff;'",
            startTime: "2024-03-05T00:00:00",
            stopTime: "2024-03-08T23:59:59",
        },
        "LTR-2024-04-23": {
            title: "营养微生态医学423研讨会",
            background: "https://totemlife.cn/apps/default-image",
            message: "营养微生态医学423研讨会福利领取",
            textStyle: "style='color:#0f4c81;'",
            description:
                "健康金豆、硫磺菌口服液、图腾益生粉剂等众多好礼你来抽，100%中奖",
            tips: "",
            comment: "",
            qrImg: "https://oss.foodtrust.cn//2d55d202401312018579476.png", //这个是U#660的蓝色公众号关注码
            refNo: "扫码领取健康金豆",
            bntName: "点击进入抽奖活动",
            bntStyle: "",
            bntLink: "/apps/loyalty/scratch-card/LTR-2024-04-23",
            startTime: "2024-04-23T00:00:00",
            stopTime: "2024-04-23T23:59:59",
        },
    };
    const detail = subjectTemplates[subject] || subjectTemplates["greeting"];
    return detail;
}

function maskString(inputString, method = "standard") {
    if (inputString === null || inputString === "") return "";
    // 检查输入是否为数字类型
    if (typeof inputString === "number" || !isNaN(parseFloat(inputString))) {
        inputString = String(Math.trunc(Number(inputString))); // 将数字转换为字符型的整数字符串
    }
    if (method === "shuffle") {
        const chunkSize = 3;
        const maskSize = 2;
        let maskedString = "";
        for (let i = 0; i < inputString.length; i += chunkSize) {
            const chunk = inputString.substring(i, i + chunkSize);
            if (chunk.length === chunkSize) {
                const maskedChunk =
                    chunk.substring(0, maskSize).replace(/./g, "*") +
                    chunk.substring(maskSize);
                maskedString += maskedChunk;
            } else {
                maskedString += chunk;
            }
        }
        return maskedString;
    } else {
        // 默认为 'standard'
        const position = Math.ceil((inputString.length * 2) / 3); // 取字符串长度的2/3，向上取整
        const maskedPortion = "**" + inputString.substring(position);
        return maskedPortion;
    }
}

function getCrmRole(roles) {
    const crmRoles = [
        "crm-admin",
        "crm-advisor",
        "crm-gmoffc",
        "crm-sales",
        "crm-service",
        "crm-channel",
        "crm-mkting",
    ];
    for (const role of crmRoles) {
        if (roles.includes(role)) return role;
    }
    return "";
}

function getTrialRole(roles) {
    const trialRoles = [
        "trial-admin",
        "trial-investigator",
        "trial-subject",
    ];
    for (const role of trialRoles) {
        if (roles.includes(role)) return role;
    }
    return "";
}

function getEmojiedUserRole(role) {
    switch (role) {
        case "crm-admin":
            return "👑";
        case "crm-gmoffc":
            return "📊";
        case "crm-sales":
            return "👨";
        case "crm-channel":
            return "💰";
        case "crm-service":
            return "👩";
        case "crm-mkting":
            return "📈";
        default:
            return ""; // 默认为空字符或其他默认字符
    }
}

function getEmojiedUserType(type) {
    switch (type) {
        case 0:
            return "👤";
        case 1:
            return "🥼";
        case 2:
            return "🤝";
        case 3:
            return "💼"; // 钱袋
        case 4:
            return "🛠️"; // 服务铃
        case 9:
            return "⚙️"; // 服务铃
        default:
            return "🤖";
    }
}

//在order的delivery_id中有时候填了多个快递单号，取出最可能的那一个
function getQualifiedWaybillId(str) {
    var words = str.split(" "); // 按空格拆分字符串
    for (var i = 0; i < words.length; i++) {
        if (words[i].length >= 12 && words[i].substring(0, 4) !== "0000") {
            return words[i]; // 如果找到符合条件的元素，则返回该元素
        }
    }
    return null; // 如果没有找到符合条件的元素，则返回null
}

async function prepareExpressData(order_id) {
    const expressData = {
        order_id: order_id, //尝试，方便直接处理订单
        openid: "", // 用户唯一标识
        receiver_phone: "", // 必填，收件人手机号
        waybill_id: "", // 必填，运单号
        goods_info: {
            detail_list: [], // 商品详细信息的数组
        },
        trans_id: "", // 必填，微信支付生成的交易单号
        order_detail_path: "", // 可选，建议为订单详情页path
    };

    // 查询订单基本信息
    const orderQuery = `select o.id, o.uid, w.routine_openid as openid, u.parent_uid as parent_uid, o.cart_id as goods_list, o.user_phone as receiver_phone, o.delivery_id as delivery_id, o.order_id as trans_id from tutem_mall.eb_store_order o left join tutem_mall.eb_wechat_user w on (o.uid = w.uid) left join tutem_mall.eb_user u on (o.uid = u.uid) where o.id = ${order_id} and o.paid = 1 and o.delivery_id is not NULL`;
    const [orderRows] = await rds.query(orderQuery);

    if (!orderRows || orderRows.length === 0) {
        return null;
    }

    const order = orderRows[0];

    // 初始化expressData对象
    expressData.openid = order.openid;
    if (!order.openid && order.parent_uid > 0) {
        const [parentRows] = await rds.query(
            `select uid, routine_openid from tutem_mall.eb_wechat_user where uid = ${order.parent_uid}`
        );
        if (parentRows.length > 0) {
            expressData.openid = parentRows[0].routine_openid;
        }
    }
    expressData.receiver_phone = order.receiver_phone;
    expressData.waybill_id = getQualifiedWaybillId(order.delivery_id);
    expressData.trans_id = order.trans_id;

    // 查询商品信息
    const goodsList = JSON.parse(order.goods_list);
    const goodsInfoQuery = `select c.id, c.product_id, p.store_name as goods_name, p.image as goods_img_url from tutem_mall.eb_store_cart c left join eb_store_product p on (c.product_id = p.id ) where c.id in (${goodsList.join(",")})`;
    const [goodsRows] = await rds.query(goodsInfoQuery);

    goodsRows.forEach((goods) => {
        expressData.goods_info.detail_list.push({
            goods_name: goods.goods_name,
            goods_img_url: goods.goods_img_url,
        });
    });

    // 如果需要的话，这里还可以添加其他可选字段的处理
    return expressData;
}

// 发送运单数据到后台
async function uploadExpress(expressData) {
    try {
        const accessToken = await wework.getWechatAccessToken("weapp");
        const response = await axios.post(
            `https://api.weixin.qq.com/cgi-bin/express/delivery/open_msg/follow_waybill?access_token=${accessToken}`,
            expressData
        );
        if (response && response.data.errcode != 0) {
            if (response.data.errcode == 931023) {
                // waybill not exist detail: [运单不存在] rid: 662a2f2f-25a6925d-05fe3291'
                const order_id = expressData.order_id;
                const orderQuery = `update tutem_mall.eb_store_order set waybill_status = -1 WHERE id = ?`;
                await rds.query(orderQuery, [order_id]);
            }
            console.log("uploadExpress ERROR: ", response.data, expressData);
        }
        return response?.data;
    } catch (error) {
        console.error("Error in uploadExpress:", error);
        return { errcode: -1, errmsg: error.message };
    }
}

// 发送运单数据到后台
async function queryFollowTrace(waybillToken, retryCount = 1) {
    try {
        const accessToken = await wework.getWechatAccessToken("weapp");
        const response = await axios.post(
            `https://api.weixin.qq.com/cgi-bin/express/delivery/open_msg/query_follow_trace?access_token=${accessToken}`,
            { waybill_token: waybillToken }
        );

        // 如果token过期且还有重试次数，则清除缓存并重试
        if (response?.data?.errcode === 42001 && retryCount > 0) {
            await wework.clearAccessTokenCache("weapp");
            return queryFollowTrace(waybillToken, retryCount - 1);
        }

        if (response?.data?.errcode !== 0) {
            console.log(
                "queryFollowTrace ERROR: ",
                response.data,
                waybillToken
            );
        }
        return response?.data ?? null;
    } catch (error) {
        console.error("Error in queryFollowTrace:", error);
        return { errcode: -1, errmsg: error.message };
    }
}

async function updateOrderWaybill(order_id) {
    const orderQuery = `SELECT id, waybill_token FROM tutem_mall.eb_store_order WHERE id = ?`;
    const [orderRow] = await rds.query(orderQuery, [order_id]);

    // 如果waybill_token不为零，则直接调用queryFollowTrace
    let waybillToken = orderRow[0]?.waybill_token;
    if (!waybillToken) {
        const expressData = await prepareExpressData(order_id);
        if (!expressData) return null;
        const result = await uploadExpress(expressData);
        if (result.errcode != 0) return result;
        waybillToken = result.waybill_token;
    }

    const ret = await queryFollowTrace(waybillToken);
    if (ret.errcode === 0) {
        if (ret.waybill_info.waybill_id) {
            //console.log(`Update waybill for order#${order_id}:`, ret);
            let formattedTrace =
                ret.waybill_info &&
                ret.waybill_info.path &&
                ret.waybill_info.path.path_action
                    ? ret.waybill_info.path.path_action
                          .map((action) => {
                              return `[${moment.unix(action.action_time).format("YYYY-MM-DD HH:mm:ss")}] ${action.action_msg}`;
                          })
                          .join("\n")
                    : "";
            const insertQuery = `INSERT INTO tutem_mall.waybill (order_id, info, waybill_id, waybill_token, status, formatted_trace, updated_at) VALUES (?, ?, ?, ?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE info = VALUES(info), waybill_token = VALUES(waybill_token), status = VALUES(status), formatted_trace = VALUES(formatted_trace), updated_at = VALUES(updated_at)`;
            await rds.query(insertQuery, [
                order_id,
                JSON.stringify(ret),
                ret.waybill_info.waybill_id,
                waybillToken,
                ret.waybill_info.status,
                formattedTrace,
            ]);

            const action = ret.waybill_info.path?.path_action[0];
            const waybillTrace = action
                ? `[${moment.unix(action.action_time).format("YYYY-MM-DD HH:mm:ss")}] ${action.action_msg}`
                : "";
            const updateQuery = `update tutem_mall.eb_store_order set waybill_status = ?, waybill_token = ?, waybill_trace = ? where id = ${order_id}`;
            await rds.query(updateQuery, [
                ret.waybill_info.status,
                waybillToken,
                waybillTrace,
            ]);
            //console.log('Done.');
            return ret;
        } else {
            console.log("Skip updating order_id: ", order_id, ret.waybill_info);
        }
    } else {
        console.error(
            `Failed to query waybill info for order_id: ${order_id} - errcode #`,
            ret.errcode,
            ret.errmsg
        );
    }
    return ret;
}

async function updateWaybills(cond = "") {
    try {
        const orderQuery = `SELECT id FROM tutem_mall.eb_store_order WHERE delivery_type = 'express' AND paid = 1 AND ( waybill_status != 4 and waybill_status != 9 and waybill_status != -1 ) AND delivery_id is not NULL ${cond} order by id desc`;
        const [orderRows] = await rds.query(orderQuery);
        for (const order of orderRows) {
            await updateOrderWaybill(order.id);
        }
        return true;
    } catch (error) {
        console.error(`Error updating waybills: `, cond);
        return false;
    }
}

// 金豆发放和扣减程序
async function grantPoints(uid, link_id, title, points, mark) {
    if (points === 0) {
        return {
            errcode: 10001,
            errmsg: `invalid points to change: ${points}`,
            bill_id: 0,
        };
    }

    // Check if the bill already exists
    let [bill] = await rds.query(
        `SELECT id FROM tutem_mall.eb_user_bill WHERE uid = ? AND link_id = ?`,
        [uid, link_id]
    );
    if (bill && bill.length > 0) {
        return {
            errcode: 10002,
            errmsg: `bill already existed link_id: ${link_id}`,
            bill_id: bill[0].id,
        };
    }

    // Check if user exists
    let [user] = await rds.query(
        `SELECT uid, integral FROM tutem_mall.eb_user WHERE uid = ?`,
        [uid]
    );
    if (!user || user.length === 0) {
        return {
            errcode: 10003,
            errmsg: `user not found uid: ${uid}`,
            bill_id: 0,
        };
    }

    let current_balance = user[0].integral;
    let new_balance = (parseFloat(current_balance) + parseFloat(points)).toFixed(2);
    if (new_balance < 0) {
        return {
            errcode: 10004,
            errmsg: `invalid new balance: ${new_balance}`,
            bill_id: 0,
        };
    }

    let now_time = Math.floor(Date.now() / 1000);

    try {
        let pm = points > 0 ? 1 : 0;
        let number = points > 0 ? points : -points;
        let type = points > 0 ? "system_add" : "system_sub";

        // 插入账单记录
        const [insertResult] = await rds.query(
            `INSERT INTO tutem_mall.eb_user_bill (uid, link_id, pm, title, category, type, number, balance, mark, add_time, status, take, is_hide)
            VALUES (?, ?, ?, ?, 'integral', ?, ?, ?, ?, ?, 1, 0, 0)`,
            [uid, link_id, pm, title, type, number, new_balance, mark, now_time]
        );

        // 更新用户积分
        await rds.query(
            `UPDATE tutem_mall.eb_user SET integral = ? WHERE uid = ?`,
            [new_balance, uid]
        );

        return { errcode: 0, errmsg: `ok`, bill_id: insertResult.insertId };
    } catch (err) {
        console.error(err);
        return {
            errcode: points > 0 ? 10005 : 10006,
            errmsg: `${points > 0 ? "add" : "deduct"} points error: ${err.message}`,
            bill_id: 0,
        };
    }
}

// 图贝发放和扣减
async function grantTubay(uid, link_id, title, tubay, mark) {
    if (tubay === 0) {
        return {
            errcode: 10001,
            errmsg: `invalid tubay to change: ${tubay}`,
            bill_id: 0,
        };
    }

    // Check if the bill already exists
    let [bill] = await rds.query(
        `SELECT id FROM tutem_mall.eb_user_bill WHERE uid = ? AND link_id = ?`,
        [uid, link_id]
    );
    if (bill && bill.length > 0) {
        return {
            errcode: 10002,
            errmsg: `bill already existed link_id: ${link_id}`,
            bill_id: bill[0].id,
        };
    }

    // Check if user exists
    let [user] = await rds.query(
        `SELECT uid, now_money FROM tutem_mall.eb_user WHERE uid = ?`,
        [uid]
    );
    if (!user || user.length === 0) {
        return {
            errcode: 10003,
            errmsg: `user not found uid: ${uid}`,
            bill_id: 0,
        };
    }

    let current_balance = user[0].now_money;
    let new_balance = (parseFloat(current_balance) + parseFloat(tubay)).toFixed(2);
    if (new_balance < 0) {
        return {
            errcode: 10004,
            errmsg: `invalid new balance: ${new_balance}`,
            bill_id: 0,
        };
    }

    let now_time = Math.floor(Date.now() / 1000);

    try {
        let pm = tubay > 0 ? 1 : 0;
        let number = tubay > 0 ? tubay : -tubay;
        let type = tubay > 0 ? "system_add" : "system_sub";

        // 插入账单记录
        const [insertResult] = await rds.query(
            `INSERT INTO tutem_mall.eb_user_bill (uid, link_id, pm, title, category, type, number, balance, mark, add_time, status, take, is_hide)
            VALUES (?, ?, ?, ?, 'now_money', ?, ?, ?, ?, ?, 1, 0, 0)`,
            [uid, link_id, pm, title, type, number, new_balance, mark, now_time]
        );

        // 更新用户图贝余额
        await rds.query(
            `UPDATE tutem_mall.eb_user SET now_money = ? WHERE uid = ?`,
            [new_balance, uid]
        );

        return { errcode: 0, errmsg: `ok`, bill_id: insertResult.insertId };
    } catch (err) {
        console.error(err);
        return {
            errcode: tubay > 0 ? 10005 : 10006,
            errmsg: `${tubay > 0 ? "add" : "deduct"} tubay error: ${err.message}`,
            bill_id: 0,
        };
    }
}

function generatePayloadText(payload) {
    const maps = {
        uid: { name: "uid", prefix: "【用户UID】", suffix: "\n" },
        channel: { name: "channel", prefix: "【了解渠道】", suffix: "\n" },
        stores: { name: "stores", prefix: "【购买途径】", suffix: "\n" },
        user: { name: "user", prefix: "【实际用户】", suffix: "\n" },
        gender: { name: "gender", prefix: "【用户性别】", suffix: "\n" },
        gendar: { name: "gendar", prefix: "【用户性别】", suffix: "\n" },
        age: { name: "age", prefix: "【用户年龄】", suffix: "\n" },
        group: { name: "group", prefix: "【用户年龄】", suffix: "\n" },
        purposes: { name: "purposes", prefix: "【针对疾病】", suffix: "\n" },
        history: { name: "history", prefix: "【疾病时间】", suffix: "\n" },
        diagnosis: { name: "diagnosis", prefix: "【疾病诊断】", suffix: "\n" },
        hospital: { name: "hospital", prefix: "【就诊机构】", suffix: "\n" },
        therapy: { name: "therapy", prefix: "【治疗方案】", suffix: "\n" },
        goods: { name: "goods", prefix: "【所用产品】", suffix: "\n" },
        time: { name: "time", prefix: "【使用时长】", suffix: "\n" },
        serve: { name: "serve", prefix: "【日均用量】", suffix: "\n" },
        symptoms: { name: "symptoms", prefix: "【症状变化】", suffix: "\n" },
        metrics: { name: "metrics", prefix: "【指标变化】", suffix: "\n" },
        proof: { name: "proof", prefix: "【检测单据】", suffix: "\n" },
        rating: { name: "rating", prefix: "【总体评价】", suffix: "\n" },
        reason: { name: "reason", prefix: "【评价原因】", suffix: "\n" },
        question: { name: "question", prefix: "【问题留言】", suffix: "\n" },
        shareFurther: {
            name: "shareFurther",
            prefix: "【可做访谈】",
            suffix: "\n",
        },
        signature: { name: "signature", prefix: "【授权签名】", suffix: "\n" },
    };

    let text = ""; // 初始化文本内容
    for (const key in payload) {
        // 遍历payload对象
        if (payload.hasOwnProperty(key)) {
            const result = maps[key];
            if (result) {
                const { prefix, suffix } = maps[key]; // 获取当前键对应的前缀和后缀
                text += `${prefix}${payload[key]}${suffix}`; // 构造并添加文本内容
            } else {
                console.log("key not existed: ", key);
            }
        }
    }
    return text; // 返回生成的文本
}

// grantReferPoints函数, 返回bill_id
async function grantReferPoints(reqData) {
    const { tuid, ruid, dest_url } = reqData;
    const day = moment().format("YYYY-MM-DD");
    const link_id = `R${ruid}-GRP-${day}-T${tuid}`;
    const category = "integral";
    const [recs] = await rds.query(
        `SELECT SUM(number) as total_points FROM tutem_mall.eb_user_bill WHERE link_id LIKE 'R${ruid}-GRP-${day}-%' AND category = 'integral'`
    );
    let bill_id = 0;
    if (recs[0].total_points > 200)
        return {
            errcode: 20001,
            errmsg: `ruid-${ruid} exceeds max daily-refer-reward cap`,
            bill_id: 0,
        };

    const pattern = `-GRP-${day}-T${tuid}`;

    let points = 0;
    // 统计link_id字段包含'GRP-${day}-T${tuid}'的记录
    const [rows] = await rds.query(
        `SELECT SUM(number) as total_points FROM tutem_mall.eb_user_bill WHERE link_id LIKE ? AND category = ?`,
        ["%" + pattern + "%", category]
    );

    if (rows[0].total_points === null || rows[0].total_points === 0) {
        // 首次阅读推荐内容
        points = 50;
    } else if (rows[0].total_points > 0 && rows[0].total_points <= 200) {
        // 阅读推荐内容未到金豆发放限额
        points = 10;
    }

    // 6. 如果应发放金豆points>0
    if (points > 0) {
        const time = moment().format("YYYY-MM-DD HH:mm:ss");
        const mark = `[${time}] tuid: ${tuid}, ruid: ${ruid}, points: ${points}, dest_url: ${dest_url}`;
        const result = await grantPoints(
            parseInt(ruid),
            link_id,
            `推荐U#${tuid}阅读成功奖励`,
            points,
            mark
        );
        logger.info("GRANT-REFER-POINTS: ", reqData, result);
        return result;
    }

    return {
        errcode: 20002,
        errmsg: `tuid-${tuid} exceeds daily-read-grant cap`,
        bill_id: 0,
    };
}

// 发放优惠券给用户
async function grantCoupon(uid, couponId, activityCode) {
    try {
        // 检查用户是否关注了公众号
        const [wuser] = await rds.query(
            'SELECT openid, subscribe FROM tutem_mall.eb_wechat_user WHERE uid = ?',
            [uid]
        );

        if (!wuser || !wuser.length || wuser[0].subscribe === 0) {
            return {
                errcode: 10008,
                errmsg: '用户需关注图腾生命医学公众号后才能发放优惠券'
            };
        }

        // 获取优惠券信息
        const [coupon] = await rds.query(
            'SELECT * FROM tutem_mall.eb_store_coupon WHERE id = ?',
            [couponId]
        );

        if (!coupon || !coupon.length) {
            return {
                errcode: 10009,
                errmsg: '优惠券不存在'
            };
        }

        try {
            // 发放优惠券
            const now = Math.floor(Date.now() / 1000);
            const [result] = await rds.query(
                `INSERT INTO tutem_mall.eb_store_coupon_user 
                (cid, uid, coupon_title, coupon_price, use_min_price, add_time, end_time, use_time, status, is_fail) 
                VALUES (?, ?, ?, ?, ?, ?, ?, 0, 0, 0)`,
                [
                    couponId,
                    uid,
                    coupon[0].title,
                    coupon[0].coupon_price,
                    coupon[0].use_min_price,
                    now,
                    now + (coupon[0].coupon_time * 86400)
                ]
            );

            // 发送微信消息通知
            const notice = `【系统发券】用户#${uid} 通过${activityCode}获得优惠券#${couponId} ${coupon[0].title}`;
            task.notifyWework(1, notice);

            return {
                errcode: 0,
                errmsg: 'ok'
            };

        } catch (error) {
            console.error('Error in coupon grant:', error);
            return {
                errcode: 10011,
                errmsg: error.message
            };
        }

    } catch (error) {
        console.error('Error in grantCoupon:', error);
        return {
            errcode: -1,
            errmsg: error.message
        };
    }
}

const tutils = {
    getSlugBasedOnDate,
    mtaCodeMapping,
    getMtaUrlByCode,
    getMtaSubject,
    registerMpAuth,
    getExternalUserids,
    getMostRecentSlug,
    getEmployeeList,
    getExternalContact,
    getAllExternalContacts,
    getSubjectTemplate,
    maskString,
    getCrmRole,
    getTrialRole,
    getEmojiedUserRole,
    getEmojiedUserType,
    prepareExpressData,
    uploadExpress,
    queryFollowTrace,
    updateWaybills,
    updateOrderWaybill,
    generatePayloadText,
    grantTubay,
    grantPoints,
    grantReferPoints,
    genWeappUrlLink,
    grantCoupon,
};

module.exports = tutils;
